package com.cn.util;

import org.apache.commons.lang3.ArrayUtils;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

/**
 * @program: client
 * @description: 数据库连接
 * @author: yihanyuan
 * @create: 2019-03-22 14:38
 **/
public class JdbcUtil {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;
    //表名开头的
    private static String firstTableName = "";

    static {
        try {
            Properties properties = new Properties();
//            properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("quartz.properties"));
//            JDBCTest.class.getResourceAsStream("/quartz.properties");
            InputStream inStream = JdbcUtil.class.getResourceAsStream("/jdbc.properties");
//            InputStream inStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("/jdbc.properties");
            //加载的方法，就是通过流找到配置文件的位置
            properties.load(inStream);
            driver = properties.getProperty("driver");
            url = properties.getProperty("URL");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * @return
     * @description 获取数据库连接
     * @author YiHanYuan
     * @params
     * @date 2019/3/22 14:40
     **/
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * @return
     * @description 关闭数据库连接资源，传入相关参数，若无则传null
     * @author YiHanYuan
     * @params
     * @date 2019/3/22 14:40
     **/
    public static void closeAll(ResultSet rs, PreparedStatement pstmt,
                                Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 类名对应表，属性对应字段
     *
     * @param obj 　　传入的对象
     * @return
     */
    public static boolean insert(Object obj) {
        boolean flag = false;
        //获取obj的Class
        Class<?> c = obj.getClass();
        //利用StringBuffer进行插入SQL语句的构造
        //通过反射获取类名映射表名
        StringBuffer sb1 = new StringBuffer("insert into " + c.getSimpleName() + "(");
        StringBuffer sb2 = new StringBuffer(" values(");
        //通过反射获取对象的属性数组
        Field[] field = c.getDeclaredFields();
        //遍历属性构造SQL语句
        for (int i = 0; i < field.length; i++) {
            if (i != field.length - 1) {
                sb1.append(CamelUtil.camelToLine(field[i].getName())).append(",");
                sb2.append("?,");
            } else {
                //最后一个要结尾无逗号
                sb1.append(CamelUtil.camelToLine(field[i].getName())).append(")");
                sb2.append("?);");
            }
        }
        String sql = sb1.append(sb2).toString();
        //获取数据库连接，进行数据库操作
        Connection conn = getConnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < field.length; i++) {
                //设置属性的可访问性，可以访问私有属性
                field[i].setAccessible(true);
                try {
                    //通过Field的get(Object)方法获取Object对象的属性值
                    //对预编译的SQL语句中的？进行赋值
                    ps.setObject(i + 1, field[i].get(obj));
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
            //返回执行的SQL语句
            System.out.println(ps);
            //执行SQL
            flag = ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭流
            closeAll(null, ps, conn);
        }
        return flag;
    }

    /**
     * 通过主键(默认第一个属性)删除对象
     *
     * @param obj
     * @return
     */
    public static boolean delete(Object obj) {
        boolean flag = false;
        //获取obj的Class
        Class<?> c = obj.getClass();
        //构造删除的SQL语句
        StringBuffer sb = new StringBuffer("delete from ");
        sb.append(firstTableName + CamelUtil.camelToLine(c.getSimpleName())).append(" where ");
        //获取对象属性数组
        Field[] field = c.getDeclaredFields();
        //设置第一个属性的可访问性
        field[0].setAccessible(true);
        //获取第一个属性的属性名构造删除sql
        sb.append(CamelUtil.camelToLine(field[0].getName())).append("=?");
        Connection conn = getConnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setObject(1, field[0].get(obj));
            System.out.println(ps);
            flag = ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            closeAll(null, ps, conn);
        }
        return flag;
    }

    /**
     * 模拟jdbc的更新操作，默认第一个属性为主键
     *
     * @param obj
     * @return
     */
    public static boolean update(Object obj) {
        boolean flag = false;
        Class<?> c = obj.getClass();
        StringBuffer sb = new StringBuffer("update " + firstTableName + c.getSimpleName() + " set ");
//        Field[] field = c.getDeclaredFields();
//        c.getSuperclass().getDeclaredFields();
        Field[] field = ArrayUtils.addAll(c.getDeclaredFields(), c.getSuperclass().getDeclaredFields());
        for (int i = 1; i < field.length; i++) {
            if (i != field.length - 1) {
                sb.append(CamelUtil.camelToLine(field[i].getName())).append("=?,");
            } else {
                sb.append(CamelUtil.camelToLine(field[i].getName())).append("=? where ");
            }
        }
        sb.append(CamelUtil.camelToLine(field[0].getName()) + "=?");
        String sql = sb.toString();
        Connection conn = getConnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 1; i < field.length; i++) {
                field[i].setAccessible(true);
                ps.setObject(i, field[i].get(obj));
            }
            field[0].setAccessible(true);
            ps.setObject(field.length, field[0].get(obj));
            System.out.println(ps);
            flag = ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            closeAll(null, ps, conn);
        }
        return flag;
    }

    /**
     * 通过对象的Class获取对应表中的所有记录
     * 有对象传过来则只取第一个参数作为查询条件
     *
     * @param
     * @return
     */
    public static <T> List<T> selectAll(Class<T> c, Object object) {
        //通过反射获取类名对应表名构造SQL语句
        String sql = "select * from " + firstTableName + c.getSimpleName() + " where 1 = 1 ";
        //用于存储查询结果
        List<T> list = new ArrayList<T>();
        //通过反射获取所有属性
        Field[] field = object.getClass().getDeclaredFields();
        StringBuffer sb = new StringBuffer();
//        for (int i = 0; i < field.length; i++) {
        try {
            field[0].setAccessible(true);
            Object key = field[0].get(object);
            if (null != key) {
                sb.append(" and ")
                        .append(CamelUtil.camelToLine(field[0].getName()))
                        .append(" = ")
                        .append(key);
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
//        }
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        sql += sb.toString();
        try {
            ps = conn.prepareStatement(sql);
            System.out.println(ps);
            //返回结果集
            rs = ps.executeQuery();
            while (rs.next()) {
                //通过反射构造一个T类型的实例
                T obj = c.newInstance();
                for (int i = 0; i < field.length; i++) {
                    //设置属性的可访问性(可以访问私有属性)
                    field[i].setAccessible(true);
                    //通过属性名获取结果集中的值赋值到实例对象中
                    if (field[i].getType() == boolean.class) {
                        field[i].set(obj, rs.getObject(CamelUtil.camelToLine(field[i].getName())) == "1" ? true : false);
                    } else {
                        field[i].set(obj, rs.getObject(CamelUtil.camelToLine(field[i].getName())));
                    }

                }
                //将实例对象添加到list集合
                list.add(obj);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            closeAll(rs, ps, conn);
        }
        return list;
    }

    /**
     * 通过传入的查询sql语句，返回map对象,如果过对应字段值为mull，则去除对应字段
     *
     * @param
     * @return
     */
    public static List<Map<Object, Object>> selectAll(String sql) {
        Connection conn = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<Object, Object>> list = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            //返回结果集
            rs = ps.executeQuery();
            //获取数据库对应的字段名，字段名为键，
            ResultSetMetaData metaData = rs.getMetaData();
            while (rs.next()) {
                Map<Object, Object> map = new HashMap<>();
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    Object o = rs.getObject(i + 1);
                    if (null != o) {
                        map.put(CamelUtil.lineToCamel(metaData.getColumnName(i + 1)), o);
                    }
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(rs, ps, conn);
        }
        return list;
    }

//    @Test
//    public void insertTest() {
//        Job job = new Job();
//        String date = new Date(2019, 3, 26).toString();
//        job.setBeginDate(date);
//        job.setEndDate(date);
//        job.setJobAddress("192.168.199.1");
//        job.setJobDesc("插入测试");
//        job.setJobGroup("1");
//        job.setJobName("测试");
//        job.setProject("测试project");
//        job.setJobId("001");
//        insert(job);
//    }
//
//    @Test
//    public void deleteTest() {
//        Job job = new Job();
//        job.setJobId("1");
//        delete(job);
//    }
//
//    @Test
//    public void updateTest() {
//        Job job = new Job();
//        String date = new Date(2018, 3, 26).toString();
//        job.setBeginDate(date);
//        job.setEndDate(date);
//        job.setJobAddress("192.168.199.2");
//        job.setJobDesc("插入测试2");
//        job.setJobGroup("2");
//        job.setJobName("测试2");
//        job.setProject("测试project2");
//        job.setJobId("001");
//        update(job);
//    }
//
//    @Test
//    public void selectTest() throws IllegalAccessException {
//        Job job = new Job();
//        job.setJobId("001");
//        selectAll(Job.class, job).stream().forEach(System.out::println);
//    }
//
//    @Test
//    public void selectAll() {
//        String sql = "Select * from tbl_job j left join tbl_scheduled s" +
//                " on s.job_id = j.job_id where j.job_id = 001";
//        List<Map<Object, Object>> list = selectAll(sql);
//        System.out.println(list);
//    }
}